﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using VideoClassLibrary;
using VideoClassLibrary.Model;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
namespace VideoClassLibrary.BusinessLogicLayer
{
   public class TeacherModule
    {
       /// <summary>
         /// 判断是否可以添加,但是教师只有真实名，应该不用验证的
         /// </summary>
         /// <returns></returns>
       public bool IsName(string name)
       {
           string sql = "select count(*) from V_teacher where VT_realname=@name ";
           SqlParameter username = new SqlParameter("@name", name);
           VDataBase vb = new VDataBase();
           object result = vb.GetDataByScalar(sql, false, username);
           int n = Convert.ToInt32(result);
           if (n != 0)
           {
               return false;

           }
           else
               return true;

       }
     
         /// <summary>
         /// 添加教师
         /// </summary>
         /// <returns></returns> 
       public int AddTeacher(V_teacher teacher)
       {
           VDataBase v = new VDataBase();
           Database db = VDataBase.CreateDatabase();
           string sqlStr = "insert into V_teacher values (@Updatete_time,@Create_time,@School,@realName,@mobile,@email,@VT_desc,@VT_Image,@VT_IsEnable);SELECT @@IDENTITY;";
           DbCommand command = db.GetSqlStringCommand(sqlStr);
           //SqlParameter p1 = new SqlParameter("@Updatete_time", teacher.update_time);
           //SqlParameter p2 = new SqlParameter("@Create_time", teacher.create_time);
           //SqlParameter p3 = new SqlParameter("@School", teacher.SchoolID);
           //SqlParameter p4 = new SqlParameter("@realName", teacher.VT_realname);
           //SqlParameter p5 = new SqlParameter("@mobile", teacher.VT_mobile);
           //SqlParameter p6 = new SqlParameter("@email", teacher.VT_email);
           //SqlParameter p7 = new SqlParameter("@VT_desc", teacher.VT_desc);
           //SqlParameter p8 = new SqlParameter("@VT_Image", string.Empty);
           //SqlParameter p9 = new SqlParameter("@VT_IsEnable", teacher.Vt_IsEnable);

           db.AddInParameter(command, "@Updatete_time", DbType.DateTime, teacher.update_time);
           db.AddInParameter(command, "@Create_time", DbType.DateTime, teacher.create_time);
           db.AddInParameter(command, "@School", DbType.Int32, teacher.SchoolID);
           db.AddInParameter(command, "@realName", DbType.AnsiString, teacher.VT_realname);
           db.AddInParameter(command, "@mobile", DbType.AnsiString, teacher.VT_mobile);
           db.AddInParameter(command, "@email", DbType.AnsiString, teacher.VT_email);
           db.AddInParameter(command, "@VT_desc", DbType.AnsiString, teacher.VT_desc);
           db.AddInParameter(command, "@VT_Image", DbType.AnsiString, teacher.Vt_Image);
           db.AddInParameter(command, "@VT_IsEnable", DbType.Int32, teacher.Vt_IsEnable);
           //v.GetDataByNonQuery(sql, false, p1, p2, p3, p4, p5, p6, p7,p8, p9); 
           int result;
           object obj = db.ExecuteScalar(command);
           if (!int.TryParse(obj.ToString(), out result))
           {
               return 0;
           }
           return result;
       }
      
         /// <summary>
         ///获取学校ID和名 
         /// </summary>
         /// <returns></returns>
       public List<V_School> GetSchool()
       {
           List<V_School> list = new List<V_School>();
           string sql = "select ID,VS_name FROM V_school";
           VDataBase vb = new VDataBase();
           SqlDataReader read = vb.GetDataByReader(sql, false);
           if (read.HasRows)
           {
               while (read.Read())
               {
                   V_School vs = new V_School();
                   vs.ID = Convert.ToInt32(read[0]);
                   vs.VS_name = read[1].ToString();
                   list.Add(vs);
               }
           }
           read.Close();
           return list;

       }
     
       /// <summary>
         /// 根据学校ID获取学校名 
         /// </summary>
         /// <returns></returns> 
       public string GetSchNameByID(int schid)
       {
           StringBuilder strSql = new StringBuilder();
            strSql.Append("select VS_name FROM V_school where ID=@sid");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "sid", DbType.Int32, schid);
            DataSet dsResult = db.ExecuteDataSet(command);
            string OSchName =null;
            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                DataRow dr = dsResult.Tables[0].Rows[0];
                OSchName = dr["VS_name"].ToString();
            }
           return OSchName;

       }
       public DataSet GetTeacherByALL(int id)
       {

           string sql = " select ID, update_time,create_time,VT_realname, VT_mobile,VT_email,VT_desc,VT_Image,VT_IsEnable from V_teacher";
           sql += " where  SchoolID=" + id+"  and VT_IsEnable=1 ";
           Database db = VDataBase.CreateDatabase();
           return db.ExecuteDataSet(CommandType.Text, sql.ToString());
       }
     
       /// <summary>
       /// yxq add
       /// </summary>
       /// <param name="id"></param>
       /// <param name="pageCount"></param>
       /// <param name="currentPage"></param>
       /// <returns></returns>
       public DataSet GetTeacherByALL(int id,int pageCount,int currentPage)
       {

           string sql = "SELECT  TOP " + pageCount + "  ID, update_time,create_time,VT_realname, VT_mobile,VT_email,VT_desc,VT_Image,VT_IsEnable from V_teacher";
          // string sql = " select ID, update_time,create_time,VT_realname, VT_mobile,VT_email,VT_desc from V_teacher";
           sql += " where  ID not in (SELECT TOP " + pageCount * currentPage + " ID  FROM V_teacher ORDER BY ID  ) and  SchoolID=" + id;      

           Database db = VDataBase.CreateDatabase();
           return db.ExecuteDataSet(CommandType.Text,sql.ToString());
       }
        /// <summary>
         /// 显示所有教师
         /// </summary>
         /// <returns></returns> 
       public List<V_teacher> GetAllteacher()
       {
           List<V_teacher> list = new List<V_teacher>();
           string sql = "  select ID, update_time,create_time,SchoolID,VT_realname, VT_mobile,VT_email,VT_desc,VT_Image,VT_IsEnable from V_teacher";
           VDataBase vb = new VDataBase();
           SqlDataReader read = vb.GetDataByReader(sql, false);
           if (read.HasRows)
           {
               while (read.Read())
               {
                   V_teacher teacher = new V_teacher();
                   teacher.ID = Convert.ToInt32(read[0]);
                   teacher.update_time = Convert.ToDateTime(read[1]);
                   teacher.create_time = Convert.ToDateTime(read[2]);
                   teacher.SchoolID = Convert.ToInt32(read[3]);
                   teacher.VT_realname = read[4].ToString();
                   teacher.VT_mobile = read[5].ToString();
                   teacher.VT_email = read[6].ToString();
                   teacher.VT_desc = read[7].ToString();
                   teacher.Vt_Image = read[8].ToString();
                   teacher.Vt_IsEnable = int.Parse(read[9].ToString());
                   list.Add(teacher);
               }
           }
           read.Close();
           return list;
       }
       /// <summary>
         /// 按姓名查找教师
         /// </summary>
         /// <returns></returns> 
       public List<V_teacher> GetAllteacherByNameAndId(string tname,int id)
       {
           List<V_teacher> list = new List<V_teacher>();
           string sql = "  select ID, update_time,create_time,VT_realname, VT_mobile,VT_email,VT_desc,VT_Image,VT_IsEnable from V_teacher where Schoolid=@id and VT_realname like'%" + tname + "%' and VT_IsEnable=1 ";
           SqlParameter p = new SqlParameter("@id",id);
           VDataBase vb = new VDataBase();
           SqlDataReader read = vb.GetDataByReader(sql, false,p);
           if (read.HasRows)
           {
               while (read.Read())
               {
                   V_teacher teacher = new V_teacher();
                   teacher.ID = Convert.ToInt32(read[0]);
                   teacher.update_time = Convert.ToDateTime(read[1]);
                   teacher.create_time = Convert.ToDateTime(read[2]);
                   
                   teacher.VT_realname = read[3].ToString();
                   teacher.VT_mobile = read[4].ToString();
                   teacher.VT_email = read[5].ToString();
                   teacher.VT_desc = read[6].ToString();
                   teacher.Vt_Image = read[7].ToString();
                   teacher.Vt_IsEnable = int.Parse(read[8].ToString());
                   list.Add(teacher);
               }
           }
           read.Close();
           return list;
       }
    
     
       #region 按学校和姓名查找教师
       public List<V_teacher> GetAllteacherByNAndS(int id,string tname)
       {
           List<V_teacher> list = new List<V_teacher>();
           string sql = "  select ID, update_time,create_time,SchoolID,VT_realname, VT_mobile,VT_email,VT_desc,VT_Image,Vt_IsEnable from V_teacher,VT_IsEnable where VT_realname=@name and SchoolID=@scid";
           SqlParameter p = new SqlParameter("@scid", id);
           SqlParameter p1 = new SqlParameter("@name", tname);
           VDataBase vb = new VDataBase();
           SqlDataReader read = vb.GetDataByReader(sql, false, p,p1);
           if (read.HasRows)
           {
               while (read.Read())
               {
                   V_teacher teacher = new V_teacher();
                   teacher.ID = Convert.ToInt32(read[0]);
                   teacher.update_time = Convert.ToDateTime(read[1]);
                   teacher.create_time = Convert.ToDateTime(read[2]);
                   teacher.SchoolID = Convert.ToInt32(read[3]);
                   teacher.VT_realname = read[4].ToString();
                   teacher.VT_mobile = read[5].ToString();
                   teacher.VT_email = read[6].ToString();
                   teacher.VT_desc = read[7].ToString();
                   teacher.Vt_Image = read[8].ToString();
                   teacher.Vt_IsEnable = int.Parse(read[9].ToString());
                   list.Add(teacher);
               }
           }
           read.Close();
           return list;
       }
       #endregion
        /// <summary>
         /// 删除教师信息
         /// </summary>
         /// <returns></returns> 
       public int DeleteTeacher(int TID)
       {
           string sql = "delete from V_teacher where ID=@TeacherID";
           SqlParameter p = new SqlParameter("@TeacherID", TID);
           VDataBase vb = new VDataBase();
           int result = vb.GetDataByNonQuery(sql, false, p);
           if (result > 0)
           { return 1; }
           else
           { return -1; }
 
       }
        /// <summary>
         /// 修改教师信息
         /// </summary>
         /// <returns></returns>
       public int UpdateTeacher(V_teacher teacher)
       {
           VDataBase v = new VDataBase();
           string sql = "update V_teacher set update_time=@Updatete_time,SchoolID=@School,VT_realname=@realName,VT_mobile=@mobile,VT_email=@email,VT_desc=@VT_desc,VT_Image=@VT_Image,VT_IsEnable=@VT_IsEnable where ID=@Schid";

           SqlParameter p1 = new SqlParameter("@Updatete_time", teacher.update_time);
           SqlParameter p2 = new SqlParameter("@School", teacher.SchoolID);
           SqlParameter p3 = new SqlParameter("@realName", teacher.VT_realname);
           SqlParameter p4 = new SqlParameter("@mobile", teacher.VT_mobile);
           SqlParameter p5 = new SqlParameter("@email", teacher.VT_email);
           SqlParameter p6 = new SqlParameter("@VT_desc", teacher.VT_desc);
           SqlParameter p7 = new SqlParameter("@Schid", teacher.ID);
           SqlParameter p8 = new SqlParameter("@VT_Image", teacher.Vt_Image);
           SqlParameter p9 = new SqlParameter("@VT_IsEnable", teacher.Vt_IsEnable);
           int i = v.GetDataByNonQuery(sql, false, p1, p2, p3, p4, p5, p6, p7,p8,p9);
           if (i > 0)
           { return 1; }
           else
           { return -1; }
       }

       public int UpdateTeacher(int id, string photo)
       {
           Database db = VDataBase.CreateDatabase();
           string sqlStr = "Update V_Teacher set Vt_Image=@Vt_Image where ID = @ID";
           DbCommand command = db.GetSqlStringCommand(sqlStr);
           db.AddInParameter(command, "ID", DbType.Int32, id);
           db.AddInParameter(command, "Vt_Image", DbType.AnsiString, photo);
           int i = db.ExecuteNonQuery(command);
           return i;
       }

       /// <summary>
       ///根据条件获取教室 信息
       /// </summary>
       /// <returns></returns>
       public DataSet GetTeacherListByWhere(string whereSql)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("select ID, update_time,create_time,SchoolID,VT_realname, VT_mobile,VT_email,VT_desc,VT_Image,VT_IsEnable ");
           strSql.Append("  from V_teacher where 1=1 ");

           if (whereSql != "")
           {
               strSql.Append(whereSql);
           }
           Database db = VDataBase.CreateDatabase();

           return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
       }
       /// <summary>
       /// 根据教师ID获取教师实体
       /// </summary>
       /// <returns></returns>
       public V_teacher GetTeacherModelById(int teacherId)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("select ID, update_time,create_time,SchoolID,VT_realname, VT_mobile,VT_email,VT_desc,VT_Image,VT_IsEnable  from V_teacher ");
           strSql.Append(" where ID=@ID ");
           Database db = VDataBase.CreateDatabase();
           DbCommand command = db.GetSqlStringCommand(strSql.ToString());
           db.AddInParameter(command, "ID", DbType.Int32, teacherId);
           DataSet dsResult = db.ExecuteDataSet(command);
           V_teacher model = null;

           if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
           {
               DataRow dr = dsResult.Tables[0].Rows[0];
               model = new V_teacher();
               model.create_time = dr["create_time"] == DBNull.Value ? System.DateTime.Now : System.DateTime.Parse(dr["create_time"].ToString());
               model.ID = int.Parse(dr["ID"].ToString());
               model.SchoolID = int.Parse(dr["SchoolID"].ToString());
               model.VT_realname = dr["VT_realname"].ToString();
               model.VT_mobile = dr["VT_mobile"].ToString();
               model.VT_email = dr["VT_email"].ToString();
               model.VT_desc = dr["VT_desc"].ToString();
               model.Vt_Image = dr["VT_Image"].ToString();
               model.Vt_IsEnable = int.Parse(dr["VT_IsEnable"].ToString());
               dsResult = null;
           }
           return model;
       }
       /// <summary>
       /// 根据 教师名称查询
       /// </summary>
       /// <param name="tname">教师姓名</param>
       /// <param name="id">schoolid</param>
       /// <returns></returns>
       public DataSet GetTeacherByNameAndId(string tname, string id)
       {
           string sql = "  select ID, update_time,create_time,VT_realname, VT_mobile,VT_email,VT_desc,VT_Image,VT_IsEnable from V_teacher where Schoolid=@id and VT_realname like'%" + tname + "%' and VT_IsEnable=1";
           Database db = VDataBase.CreateDatabase();
           DbCommand command = db.GetSqlStringCommand(sql.ToString());
           db.AddInParameter(command, "tname", DbType.String,tname);
           db.AddInParameter(command, "ID", DbType.String, id);
           return db.ExecuteDataSet(command);
       }
       /// <summary>
       /// 根据学校Id查询教师
       /// </summary>
       /// <param name="sid">学校ID</param>
       /// <returns></returns>
       public DataSet GetTeacherBysSchoolId(int sid)
       {
           string sql = "  select top(3) ID, update_time,create_time,VT_realname, VT_mobile,VT_email,VT_desc,VT_Image,VT_IsEnable from V_teacher where Schoolid=@SchoolID order by ID desc";
           Database db = VDataBase.CreateDatabase();
           DbCommand command = db.GetSqlStringCommand(sql.ToString());
           db.AddInParameter(command, "SchoolID", DbType.String, sid);
           return db.ExecuteDataSet(command);
       }
       
    }
}
